Aggregate Methods

Aggregate Methods#

In this lesson, we’ll demonstrate working with a few of the important aggregate functions.

Example Syntax#

SELECT AggregateFunction(col1)

FROM table;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/22lesson.sh and wait for the MySQL prompt to start-up.

1. We can count the number of rows in a table using the COUNT function.

   SELECT COUNT(*) FROM Actors;
   

Note the output of the query is a single value rather than rows.

2. Using the SUM function, we can add up the numeric values of a column. For instance, the following query will sum the net worth of all the actors in our example setup to report the cumulative worth of all the actors.

   SELECT SUM(NetworthInMillions) FROM Actors;
   

3. We can use the AVG function to calculate the average net worth of actors as follows:

   SELECT AVG(NetWorthInMillions) FROM Actors;
   

4. We can find the actor with the least net worth as follows:

   SELECT MIN(NetWorthInMillions) FROM Actors;
   

5. Similarly, we can find the actor with the most net worth as follows:

   SELECT MAX(NetWorthInMillions) FROM Actors;
   

   Note that we can also apply the MIN and MAX functions to non-numerical columns such as FirstName. MySQL would return the actor with the first name that occurs first or last when first names are sorted for MIN and MAX respectively. The queries are shown below:

   SELECT MAX(FirstName) FROM Actors;
   SELECT MIN(FirstName) FROM Actors;
   

6. We can find the income disparity among actors using the standard deviation function STD or STDDEV as follows:

   SELECT STDDEV(NetWorthInMillions) FROM Actors;
   

You can find a comprehensive list of MySQL functions here.